﻿using Beryl.Util;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.NetworkInformation;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Services.Description;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace Beryl
{
    public class DBOperation: IDisposable
    {
        public static SqlConnection sqlCon;

        private string ConServerStr = @"Data Source=192.168.1.161;Initial Catalog=BERYL_AMS20200702;Persist Security Info=True;User ID=sa;Password=admin@123";

        //默认构造函数
        public DBOperation()
        {
            if(sqlCon == null)
            {
                sqlCon = new SqlConnection();
                sqlCon.ConnectionString = ConServerStr;
                sqlCon.Open();
            }
        }
        //关闭/销毁函数，相当于Close()
        public void Dispose()
        {
            if (sqlCon != null)
            {
                sqlCon.Close();
                sqlCon = null;
            }
        }

        /// <summary>
        /// 获取所有货物的信息
        /// </summary>
        /// <returns>所有货物信息</returns>
        //public List<string> selectAllCargoInfor(string User)
        //{
        //    List<string> list = new List<string>();
        //    try
        //    {
        //        string sql = "select homePage from SYS_User where LoginUser = '"+ User +"'";
        //        SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //        //命令为存储过程
        //        //cmd.CommandType = CommandType.StoredProcedure;
        //        SqlDataReader reader = cmd.ExecuteReader();
        //        if(reader.Read() == true)//能查到
        //        {
        //            for (int i = 0; i < reader.FieldCount; i++)
        //            {
        //                list.Add(reader[i].ToString());
        //            }
        //        }
        //        else // 不能查到
        //        {
        //            list.Add("failed:找不到账号!请检查账号是否正确。");
        //            string hingeString = Newtonsoft.Json.JsonConvert.SerializeObject(list);
        //            LogUtil.Error(hingeString);
        //        }
        //        reader.Close();
        //        cmd.Dispose();
        //    }
        //    catch (Exception e)
        //    {
        //        list.Add("failed:" + e.Message.ToString());
        //        LogUtil.Error(list[0].ToString());
        //    }
        //    return list;
        //}

        //裁切查询barcode
        //public string selectbarCode(string barCode)
        //{
           
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    string strPicYZ = "";
        //    try
        //    {
        //        //string sql = "select a.cMOLotNo,a.cBatchNumber,a.cOFlag,b.leng,a.dEffWidth,a.dLotQty," +
        //        //    "a.cxx,a.csx,a.cMatNo from APS_ProLotOrder a join(select max(dLength) as leng,cOrderNo " +
        //        //    "from[dbo].[APS_ValidBOM] group by cOrderNo) b on a.cMOLotNo = b.cOrderNo where a.cBarCode = '" + barCode + "'";

        //        //SqlHelper.IniConnString();
        //        //var connstr = SqlHelper.GetConnSting(); 
        //        //SqlDataReader reader = SqlHelper.ExecuteReader(connstr, CommandType.Text, sql);
        //        //if (reader.Read() == true)
        //        //{
        //        //    for(int i = 0; i < reader.FieldCount; i++) {
        //        //        list.Add(reader[i].ToString());   
        //        //    }
        //        //    String hingeString = Newtonsoft.Json.JsonConvert.SerializeObject(list);
        //        //    LogUtil.Log(name + hingeString);//将方法名写进
        //        //    if (!CheckData.Instance.startPro(list))
        //        //    {
        //        //        list1.Add("工单已开始不能重复开工!");
        //        //        return list1;
        //        //    }
        //        //}
        //        //else
        //        //{
        //        //    list.Add("找不到barcode");
        //        //}

        //        strPicYZ = JsonConvert.SerializeObject(QuerybarCode(barCode));//转为json
        //        LogUtil.Log(name + strPicYZ);//将方法名写进
        //        if (strPicYZ.Equals("[]"))
        //        {
        //            return "裁切单号为空";
        //        }
        //        else
        //        {
        //            Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(strPicYZ);
        //            string cOFlag = jsonArr[0]["cOFlag"].ToString();
        //            if(!cOFlag.Equals("0"))
        //            {
        //                return "工单已开始不能重复开工";
        //            }
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        return e.Message;
        //    }
        //    //int t =  strPicYZ.LastIndexOf("[");//索引为1
        //    strPicYZ = strPicYZ.Remove(0, 1);
        //    strPicYZ = strPicYZ.Remove(strPicYZ.Length - 1, 1);
        //    return strPicYZ;
        //}

        //查找设备
        //public string selectgetEQ(string eq)
        //{
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    string eq1;
        //    try
        //    {
        //        eq1 = JsonConvert.SerializeObject(QueryEQ(eq));//转为json
        //        LogUtil.Log(name + eq1);//将方法名写进
        //        if (eq1.Equals("[]"))
        //        {
        //            return "设备不存在或未维护";
        //        }
        //        eq1 = eq1.Remove(0, 1);
        //        eq1 = eq1.Remove(eq1.Length - 1, 1);
        //    }
        //    catch(Exception e)
        //    {
        //        return e.Message;
        //    }
        //    return eq1;
        //}

        ////查找员工
        //public string selectgetPr(string Pr)
        //{
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    string Pr1;
        //    try
        //    {
        //        Pr1 = JsonConvert.SerializeObject(QueryPr(Pr));//转为json
        //        LogUtil.Log(name + Pr1);//将方法名写进
        //        if (Pr1.Equals("[]"))
        //        {
        //            return "扫描的工号错误";
        //        }
        //        Pr1 = Pr1.Remove(0, 1);
        //        Pr1 = Pr1.Remove(Pr1.Length - 1, 1);
        //    }
        //    catch (Exception e)
        //    {
        //        return e.Message;
        //    }

        //    return Pr1;
        //}

        ////提交逻辑
        //public string summit(string summit)
        //{
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    try
        //    {
        //        summit = "[" + summit + "]";
        //        Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(summit);
        //        var k = jsonArr[0];
        //        //string sql = "insert into PMS_CutStartRecord (cBarCode,cMOLotNo,cStartTotalLength,cToolNo,cEqNo,cPsNo,cPsName,cCreateTime,isComplete) values" +
        //        //    " ('" + k["cBarCode"] + "'," + k["cMOLotNo"] + ")";
        //       string result =  CheckData.Instance.KnStatus(k["cToolNo"].ToString(), k["cStartTotalLength"].ToString());
               
        //        string sql = string.Format(@"insert into PMS_CutStartRecord (cBarCode,cMOLotNo,cStartTotalLength,cToolNo,cEqNo,cPsNo,cPsName,cCreateTime,isComplete) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
        //            k["cBarCode"].ToString(), k["cMOLotNo"].ToString(), k["cStartTotalLength"].ToString(),
        //            k["cToolNo"].ToString(), k["cEqNo"].ToString(), k["cPsNo"].ToString(), k["cPsName"].ToString(), k["cCreateTime"].ToString(), k["isComplete"].ToString());

        //        string sql1 = string.Format(@"update APS_CutProLotOrder set isReport = '1' where cOrderNo = '{0}'", k["cMOLotNo"].ToString());

        //        string sql2 = string.Format(@"update APS_ProLotOrder set cOFlag = 1 where cOrderNo = '{0}'", k["cMOLotNo"].ToString());

        //        string sql3 = string.Format(@"update PMS_BladeGroup set cStatue = '{0}' where cOrderNo = '{1}'",result, k["cToolNo"].ToString());

        //        string sql4 = sql + ";" + sql1 + ";" + sql2 + ";" + sql3;

        //        SqlCommand cmd = new SqlCommand(sql4, sqlCon);
        //        cmd.ExecuteNonQuery();
        //        cmd.Dispose();
        //        LogUtil.Log(name + sql4);//将方法名写进
        //        return "0";
        //    }
        //    catch (Exception e)
        //    {
        //        return e.Message;
        //    }

        //}

        ////裁切报工提交逻辑
        //public string summitCQ(string data)
        //{
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    try
        //    {
        //        data = "[" + data + "]";
        //        Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(data);//解析为数组
        //        var k = jsonArr[0];
        //        string sql = "insert into PMS_UnCutStock(cMOLotNo, cBatchNumber, cMatNo, cProductDepNo, cSpecifucation, dArea, dLength, dWidth, isSync, createTime) " +
        //            "values('" + k["cMOLotNo"].ToString() + "','" + k["cBatchNumber"].ToString() + "','"+ k["cMatNo"].ToString() + "','"+ k["cProductDepNo"].ToString() + "','"+ k["cSpecifucation"].ToString() + "'," + Double.Parse(k["dArea"].ToString()) + "," + Double.Parse(k["dLength"].ToString()) +","+ Double.Parse(k["dWidth"].ToString()) + ",'" + k["isSync"].ToString() +"','" + k["ReportTime"].ToString() + "')";

        //        string sql1 = string.Format(@"insert into APS_PhistoryTable (cMOLotNo,Operator,GNumber,ReportTime,NYR,isSyncReport) values ('{0}','{1}','{2}','{3}','{4}','0')", k["cMOLotNo"].ToString(), k["Operator"].ToString(), k["GNumber"].ToString(), k["ReportTime"].ToString(), k["NYR"].ToString());

        //        string sql2 = string.Format(@"update APS_ProLotOrder set cStatusNo = '0090' where cMOLotNo = '{0}'", k["cMOLotNo"].ToString());

        //        string sql3 = string.Format(@"update APS_CutProLotOrder set isReport = '2' where cOrderNo = '{0}'", k["cMOLotNo"].ToString());

        //        string sql4 = sql + ";" + sql1 + ";" + sql2 + ";" + sql3;

        //        SqlCommand cmd = new SqlCommand(sql4, sqlCon);
        //        cmd.ExecuteNonQuery();
        //        cmd.Dispose();
        //        LogUtil.Log(name + sql4);//将方法名写进
        //    }
        //    catch(Exception e)
        //    {
        //        return e.Message;
        //    }
        //    return "0";
        //}




        ////裁切报工查询
        //public List<string> getCaiQie(string data)
        //{
        //    List<string> list = new List<string>();
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    string strPicYZ1 = "";
        //    try
        //    {
        //        string strPicYZ = JsonConvert.SerializeObject(QueryCQ(data));
        //        strPicYZ1 = JsonConvert.SerializeObject(QueryPCQ(data.Split('-')[1]));//转为json 
        //        if (strPicYZ.Equals("[]") || strPicYZ1.Equals("[]"))
        //        {
        //            list.Add("此流传单号错误");
        //            return list;
        //        }
        //        strPicYZ = strPicYZ.Remove(0, 1);
        //        strPicYZ = strPicYZ.Remove(strPicYZ.Length - 1, 1);
        //        strPicYZ1 = strPicYZ1.Remove(0, 1);
        //        strPicYZ1 = strPicYZ1.Remove(strPicYZ1.Length - 1, 1);

        //        list.Add(strPicYZ);
        //        list.Add(strPicYZ1);
        //        return list;
        //    }
        //    catch(Exception e)
        //    {
        //        list.Add(e.Message);
        //        return list;
        //    }
        //}

        //public List<string> getCQReV(string data)
        //{
        //    List<string> list = new List<string>();
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    string strPicYZ1 = "";
        //    try
        //    {
        //        strPicYZ1 = JsonConvert.SerializeObject(queryCQRev(data));
        //        if(strPicYZ1.Equals("[]"))
        //        {
        //            list.Add("此流传单号错误");
        //            return list;
        //        }
        //        list.Add(strPicYZ1);

        //    }catch(Exception e)
        //    {
        //        list.Add(e.Message);
        //        return list;
        //    }
        //    return list;
        //}

        //public string selectgetKn(string kn)
        //{
        //    //try
        //    //{
        //    //    string sql = "select * from PMS_BladeGroup where cBladeGroupNum = '"+ kn +"'";
        //    //    SqlCommand cmd = new SqlCommand(sql, sqlCon);
        //    //    SqlDataReader reader = cmd.ExecuteReader();
        //    //    while (reader.Read())
        //    //    {
        //    //        for (int i = 0; i < reader.FieldCount; i++)
        //    //        {
        //    //            list.Add(reader[i].ToString());
        //    //        }
        //    //    }
        //    //    reader.Close();
        //    //    cmd.Dispose();
        //    //}
        //    //catch(Exception e)
        //    //{
        //    //    return null;
        //    //}
        //    //return list;
        //    kn = "[" + kn + "]";
        //    Newtonsoft.Json.Linq.JArray jsonArr = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(kn);
        //    var k = jsonArr[0];
        //    var Kn = QueryKn(k["Kn"].ToString());
        //    var KnRow = Kn.Select($"cBladeGroupNum = '{k["Kn"]}'");
        //    string Long = k["Long"].ToString();
        //    string isOK;
        //    if (KnRow.Length > 0)
        //    {
        //        var Kn1 = KnRow[0];
        //        if (Kn1["cStatue"].ToString().Equals("报废") || Kn1["cStatue"].ToString().Equals("废弃"))
        //        {
        //            isOK = "0";
        //        }
        //        else
        //        {
        //            string dStandardMeter = Kn1["dStandardMeter"].ToString();
        //            string dCumulativeMeter = Kn1["dCumulativeMeter"].ToString();
        //            isOK = CheckData.Instance.checkKn(dStandardMeter, Long, dCumulativeMeter);
        //        }
        //    }
        //    else
        //    {
        //        isOK = "0";
        //    }

        //    return isOK;
        //}



        ////裁切接收提交
        //public string summitCQReV(string para)
        //{
        //    string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
        //    JArray jsonArr = (JArray)JsonConvert.DeserializeObject(para);
        //    string updateSql = "";
        //    string insertSql1 = "INSERT INTO WMS_Receive(cMatNo, Category, createTime, cMOLotNo, cBatch, fQuantity, cSpecifucation, dProductDate, dInputDate, cProductDepNo, warehousecode, cSupplier, inum, irate, cPlace) VALUES";
        //    string insertSql2 = "INSERT INTO WMS_Retrieval(cMatNo, Category, createTime, cOrderNo, cMOLotNo, fQuantity, dProductDate, dOutputDate, warehousecode, cSupplier, cProductDepNo, inum, irate) VALUES";
        //    try
        //    {
        //        for (int i = 0; i < jsonArr.Count; i++)
        //        {
        //            var resData = jsonArr[i];
        //            string cMatNo = resData["物料编码"].ToString(); //已切料编码
        //            string cMatNo1 = resData["cMatNo1"].ToString(); //未切料编码
        //            double dLength = double.Parse(resData["长度(mm)"].ToString()); //长度
        //            double dWidth = double.Parse(resData["宽度(mm)"].ToString()); //宽度
        //            double rkWeight = 0.00;
        //            if (resData["重量(kg)"].ToString() != "")
        //            {
        //                rkWeight = double.Parse(resData["重量(kg)"].ToString()); //入库重量
        //            }
        //            double dPlanQty = double.Parse(resData["片数"].ToString()); //片数
        //            double ddz = double.Parse(resData["单重"].ToString()); //单重
        //            double dReqQty = double.Parse(resData["dReqQty"].ToString()); //计划数
        //            //var cateNo = cMatNo.substring(0, 5); //已切
        //            var cateNo = resData["物料大类"].ToString();
        //            var cateNo1 = cMatNo1.Substring(0, 5); //未切

        //            double fQuantity = 0; //已切主计量
        //            double inum = 0; //已切副计量
        //            double irate = 0; //已切换算率
        //            double fQuantity1 = 0; //未切主计量
        //            double inum1 = 0; //未切副计量
        //            double irate1 = 0; //未切换算率

        //            if (cateNo == "Y0104" || cateNo == "Y0192" || cateNo == "Y0105")
        //            { //已切正负箔
        //                fQuantity = Math.Round(ddz * rkWeight / dWidth * 1000,2); //单位米数
        //                inum = Math.Round(rkWeight,2); //单位kg
        //                irate = Math.Round(1 / ddz ,6);
        //            }

        //            if (cateNo == "Y0106") //已切电解纸
        //            {
        //                fQuantity = Math.Round(rkWeight,2); //单位kg
        //                inum = Math.Round(dWidth * dPlanQty * dLength / 1000,2); //单位平方米
        //            }

        //            if (cateNo1 == "Y0101" || cateNo1 == "Y0191" || cateNo1 == "Y0102")
        //            { //未切正负箔
        //                fQuantity1 = Math.Round(ddz * rkWeight,2); //单位平方米
        //                inum1 = Math.Round(rkWeight,2); //单位kg
        //                irate1 = Math.Round(1 / ddz,6);
        //            }

        //            if (cateNo1 == "Y0103") //未切电解纸
        //            {
        //                fQuantity1 = Math.Round(rkWeight,2); //单位kg
        //                inum1 = Math.Round(ddz * rkWeight,2); //单位平方米
        //            }

        //            string cBatchNumber = resData["出厂编号"].ToString() == null ? "" : resData["出厂编号"].ToString(); //未切料批次号
        //            string cMOLotNo = resData["cMOLotNo"].ToString(); //生产批次号
        //            string cOrderNo = resData["cOrderNo"].ToString(); ; //生产订单号
        //            string cSpecifution = resData["规格型号"].ToString() == null ? "" : resData["规格型号"].ToString(); //已切料规格
        //            string dProductDate = resData["dProductDate"].ToString(); //已切料生产日期
        //            string times = DateTime.Now.ToString();
        //            string dInputDate = times;
        //            string dOutputDate = times;
        //            string cSupplierInfo = resData["cSupplierInfo"].ToString() == null ? "" : resData["cSupplierInfo"].ToString(); //未切料供应商名称
        //            string cPlace = resData["库位"].ToString() == null ? "" : resData["库位"].ToString(); //入库货位
        //            string cProductDepNo = resData["cProductDepNo"].ToString();
        //            string warehousecode = resData["warehousecode"].ToString(); //wh; //入库仓库

        //            string warehousecode1 = "21-1"; //出库仓库

        //            if (i < jsonArr.Count - 1)
        //            {
        //                //已切料写入入库台账WMS_Receive
        //                insertSql1 +=  "('" + cMatNo + "'," + 3 + ",'" + times +
        //                    "','" + cMOLotNo + "','" + cOrderNo + "','" + fQuantity + "','" + cSpecifution + 
        //                    "','" + dProductDate + "','" + dInputDate + "','" + cProductDepNo + "','" + warehousecode + "','" + cSupplierInfo + "','" + inum + "'," + irate + ",'" + cPlace + "'),";

        //                insertSql2 += "('" + cMatNo1 + "'," + 3 + ",'" + times +
        //                    "','" + cMOLotNo + "','" + cBatchNumber + "','" + fQuantity1 + "','" + dProductDate + 
        //                    "','" + dOutputDate + "','" + warehousecode1 + "','" + cSupplierInfo + "','" + cProductDepNo + "','" + inum1 + "'," + irate1 + "),";
        //            }
        //            else
        //            {
        //                //已切料写入入库台账WMS_Receive
        //                insertSql1 += "('" + cMatNo + "'," + 3 + ",'" + times +
        //                   "','" + cMOLotNo + "','" + cOrderNo + "','" + fQuantity + "','" + cSpecifution +
        //                   "','" + dProductDate + "','" + dInputDate + "','" + cProductDepNo + "','" + warehousecode + "','" + cSupplierInfo + "','" + inum + "'," + irate + ",'" + cPlace + "');";

        //                insertSql2 += "('" + cMatNo1 + "'," + 3 + ",'" + times +
        //                    "','" + cMOLotNo + "','" + cBatchNumber + "','" + fQuantity1 + "','" + dProductDate +
        //                    "','" + dOutputDate + "','" + warehousecode1 + "','" + cSupplierInfo + "','" + cProductDepNo + "','" + inum1 + "'," + irate1 + ");";
        //            }
        //            updateSql += string.Format(@"update APS_ValidBOM set cCutFlag = '0' where cOrderType = '3' and cOrderNo = '{0}' and cSpecifution = '{1}' and cBatchNumber = '{2}';", cOrderNo, cSpecifution, cBatchNumber);
        //        }
        //        string sql4 = insertSql1 + insertSql2 + updateSql;
        //        //执行操作
        //        //SqlCommand cmd = new SqlCommand(sql4, sqlCon);
        //        //cmd.ExecuteNonQuery();
        //        //cmd.Dispose();

        //    }
        //    catch(Exception e)
        //    {
        //        return e.Message;
        //    }
            
        //    return "OK";
        //}

        //查询裁切接收
        public static DataTable queryCQRev(string para)
        {
            string sql = string.Format(@"select * from View_CutReceive where cBarCode = '{0}' order by dWidth", para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql).Tables[0];
            return dt;
        }


        //查找设备
        private static DataTable QueryEQ(string para)
        {
            string currentProcessName = "裁切";
            string sql = string.Format(@"select cDeviceName,cSuperviseNo from EM_EquipmentLedger where cDepartment = '{0}' and barcode = '{1}'", currentProcessName,para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql).Tables[0];
            return dt;
        }

        //查找人员
        private static DataTable QueryPr(string para)
        {
            string sql = string.Format(@"select * from Sys_PsnNum where cPsn_Num = '{0}'", para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql).Tables[0];
            return dt;
        }

        //查询刀具
        public static DataTable QueryKn(string para)
        {
            string sql = string.Format(@"select * from PMS_BladeGroup where cBladeGroupNum = '{0}'", para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql).Tables[0];
            return dt;
        }
        //查询裁切
        private static DataTable QuerybarCode(string para)
        {
            string sql = @"select a.cMOLotNo,a.cBatchNumber,a.cOFlag,b.leng,a.dEffWidth,a.dLotQty," +
                    "a.cxx,a.csx,a.cMatNo from APS_ProLotOrder a join(select max(dLength) as leng,cOrderNo " +
                    "from[dbo].[APS_ValidBOM] group by cOrderNo) b on a.cMOLotNo = b.cOrderNo where a.cBarCode = '{0}'";

            string sql1 = string.Format(sql, para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql1).Tables[0];
            return dt;
        }

        //查询裁切报工
        private static DataTable QueryCQ(string para)
        {
            string sql = @"select a.cMOLotNo,a.cBatchNumber,a.dEffWidth,a.dLotQty,b.cSpecifucation,c.dEffWidth1,c.dLength1,c.dReqQty,a.cMatNo,a.cProductDepNo from APS_ProLotOrder a left join APS_MatData_V b on a.cMatNo = b.cMatNo left join (select cOrderNo,SUM(dReqQty) as dReqQty,SUM(dPlanQty * dWidth) as dEffWidth1,MAX(dLength) as dLength1 from APS_ValidBOM where cOrderType = 3 group by cOrderNo) c on c.cOrderNo = a.cMOLotNo where a.cBarCode = '{0}'";

            string sql1 = string.Format(sql, para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql1).Tables[0];
            return dt;
        }

        //查询裁切已切料信息
        private static DataTable QueryPCQ(string para)
        {
            string sql = @"SELECT cSpecifution, dWidth, dPlanQty as dPiece, dLength, cOrderNo,dReqQty,dWidth * dPlanQty as dEffWidth FROM APS_ValidBOM where cOrderNo = '{0}' order by dWidth";

            string sql1 = string.Format(sql, para);
            //Console.WriteLine(sql);
            SqlHelper.IniConnString();
            var connstr = SqlHelper.GetConnSting();
            DataTable dt = SqlHelper.ExecuteDataset(connstr, CommandType.Text, sql1).Tables[0];
            return dt;
        }

        /// <summary>
        /// 增加一条货物信息
        /// </summary>
        /// <param name="Cname">货物名称</param>
        /// <param name="Cnum">货物数量</param>
        public bool insertCargoInfo(string Cname, int Cnum)
        {
            try
            {
                string sql = "insert into[dbo].[C] (Cname,Cnum) values ('" + Cname + "'," + Cnum + ")";
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条货物信息
        /// </summary>
        /// <param name="Cno">货物编号</param>
        public bool deleteCargoInfo(string Cno)
        {
            try
            {
                string sql = "delete from C where Cno=" + Cno;
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }


        //调用存储过程例子
        public bool insert1(string cm,string t)
        {
            int i = 0;
            string name = System.Reflection.MethodBase.GetCurrentMethod().Name;
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("cMOLotNo", typeof(String));
                dt.Columns.Add("cProcss", typeof(String));
                DataRow dr;
                dr = dt.NewRow();
                dr["cMOLotNo"] = cm;
                dr["cProcss"] = t;
                dt.Rows.Add(dr);
                SqlHelper.IniConnString();
                var connstr = SqlHelper.GetConnSting();
                i = SqlHelper.ExecuteNonQueryTypedParams(connstr, "CheckTotal", dr); //checkTotal 为存储过程名称
                
            }
            catch(Exception ex)
            {
                LogUtil.Error(name + ex.Message);
                return false;
            }
            LogUtil.Log(name + ":插入成功!"+ "受影响:" + i);
            return true;
        }
    }
}